import random

import MySQL


def sampling(date, hour, size=1000000):
    for no in range(10):
        table_name = 'raw_sample_{date}_{hour}_{no}'.format(
                date=date,
                hour=hour,
                no=no
            )
        sql = 'CREATE TABLE {table_name} LIKE raw_pagecount'.format(table_name=table_name)
        MySQL.execute(sql)
        sql = 'INSERT INTO {table_name} SELECT * FROM raw_20160101_00 ORDER BY RAND() LIMIT {size}'.format(
            table_name=table_name,
            size=size
        )
        MySQL.execute(sql)


def sampling_in_memory(date, hour, size=1000000):
    table_name = 'raw_sample_{date}_{hour}_{no}'.format(
            date=date,
            hour=hour,
            no=11
        )
    sql = 'CREATE TABLE {table_name} LIKE raw_pagecount'.format(table_name=table_name)
    MySQL.execute(sql)
    sql = 'SELECT * FROM raw_20160101_00'
    rows = MySQL.query(sql)
    samples = random.sample(rows, size)
    p_id = 0
    objs = []
    for sample in samples:
        p_id += 1
        objs.append({
            'id': p_id,
            'date': date,
            'hour': hour,
            'project': sample['project'],
            'page': sample['page'],
            'count': sample['count'],
            'size': sample['size']
        })
        if p_id % 1000 == 0:
            MySQL.insert(table_name, objs)
            objs = []
    MySQL.insert(table_name, objs)


def sampling_justly(date='20160101', hour=0, size=2000000):
    source_table = 'raw_split_' + date + '_' + str('%02d' % hour)
    sql = 'SELECT * FROM {source_table}'.format(source_table=source_table)
    rows = MySQL.query(sql)
    for no in range(1):
        table_name = 'raw_sample_{date}_{hour}_{no}'.format(
                date=date,
                hour=hour,
                no=no
            )
        sql = 'CREATE TABLE {table_name} LIKE {source_table}'.format(table_name=table_name, source_table=source_table)
        MySQL.execute(sql)
        samples = random.sample(rows, size)
        p_id = 0
        objs = []
        for sample in samples:
            p_id += 1
            objs.append({
                'id': p_id,
                'project': sample['project'],
                'page': sample['page'],
                'count': sample['count']
            })
            if p_id % 1000 == 0:
                MySQL.insert(table_name, objs)
                objs = []
        if len(objs) > 0:
            MySQL.insert(table_name, objs)


def sampling_justly_for_range(date='20160101', hour_start=0, hour_end=1, size=2000000):
    rows = []
    for hour in range(hour_start, hour_end + 1):
        source_table = 'raw_split_' + date + '_' + str('%02d' % hour)
        sql = 'SELECT * FROM {source_table}'.format(source_table=source_table)
        rows = rows.append(MySQL.query(sql))
    table_name = 'raw_sample_{date}_{hour_start}_{hour_end}'.format(
        date=date,
        hour_start=hour_start,
        hour_end=hour_end
    )
    source_table = 'raw_split_' + date + '_' + str('%02d' % hour_start)
    sql = 'CREATE TABLE {table_name} LIKE {source_table}'.format(table_name=table_name, source_table=source_table)
    MySQL.execute(sql)
    samples = random.sample(rows, size)
    p_id = 0
    objs = []
    for sample in samples:
        p_id += 1
        objs.append({
            'id': p_id,
            'project': sample['project'],
            'page': sample['page'],
            'count': sample['count']
        })
        if p_id % 1000 == 0:
            MySQL.insert(table_name, objs)
            objs = []
    if len(objs) > 0:
        MySQL.insert(table_name, objs)


def split(date='20160101', hour=0):
    source_table = 'raw_' + date + '_' + str('%02d' % hour)
    table_name = 'raw_split_' + date + '_' + str('%02d' % hour)
    sql = 'CREATE TABLE {table_name} LIKE {source_table}'.format(table_name=table_name, source_table=source_table)
    MySQL.execute(sql)
    sql = 'SELECT * FROM {source_table}'.format(source_table=source_table)
    rows = MySQL.query(sql)
    p_id = 0
    objs = []
    for row in rows:
        count = row['count']
        for i in range(count):
            p_id += 1
            objs.append({
                'id': p_id,
                'project': row['project'],
                'page': row['page'],
                'count': 1,
                'size': row['size']
            })
            if p_id % 1000 == 0:
                MySQL.insert(table_name, objs)
                objs = []
    if len(objs) > 0:
        MySQL.insert(table_name, objs)


if __name__ == '__main__':
    MySQL.init('wiki')
    # sampling(20160101, 0)
    # sampling_in_memory(20160101, 0)
    # sampling_justly('20160101', 0)
    sampling_justly_for_range()
    # split(hour=1)